﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;


using System.Web;

using System.Configuration;

using SIPO.Library.DataAccess.Report;
using SIPO.Library.DomainObject.Transaction;
using SIPO.Library.DataAccess.Administration.Catalog;
using SIPO.Library.DomainObject.Administration.Catalog;
using SIPO.Library.DataAccess.Transaction;

using System.Threading.Tasks;



namespace SIPO.Library.DataAccess.Report
{
    public class rptDashboardRepo
    {
        private string connectionString;

         public rptDashboardRepo(string ConnectionString)
        {
            this.connectionString = ConnectionString;
        }

        public DataTable PeriodeLaporan(string periode,string view)
        {
            DataTable result = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select distinct "+ periode +" from "+view;
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;
                    try
                    {
                        conn.Open();
                        da.Fill(result);
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
          
            return result;
        }
          public DataTable GetLaporan(string periode,string view,string name)
        {
            DataTable result = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select * from " + view + " where " + name + " ='" + periode + "' and " + name + " is not null and sectorname is not null order by sectorname desc";
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;
                    try
                    {
                        conn.Open();
                        da.Fill(result);
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
          
            return result;
        }
          public DataTable GetLaporanWithoutPeriode( string view)
          {
              DataTable result = new DataTable();
              using (SqlConnection conn = new SqlConnection(connectionString))
              {
                  using (SqlCommand cmd = conn.CreateCommand())
                  {
                      cmd.CommandText = "select * from " + view + " where sectorname is not null order by sectorname desc";
                      SqlDataAdapter da = new SqlDataAdapter();
                      da.SelectCommand = cmd;
                      try
                      {
                          conn.Open();
                          da.Fill(result);
                      }
                      catch (Exception ex)
                      {
                          throw (ex);
                      }
                      finally
                      {
                          if (conn.State == ConnectionState.Open) conn.Close();
                      }
                  }
              }

              return result;
          }
          public DataTable GetLaporanUpayaPen(string periode, string view, string name)
          {
              DataTable result = new DataTable();
              using (SqlConnection conn = new SqlConnection(connectionString))
              {
                  using (SqlCommand cmd = conn.CreateCommand())
                  {
                      cmd.CommandText = "select * from " + view + " where " + name + " ='" + periode + "'";
                      SqlDataAdapter da = new SqlDataAdapter();
                      da.SelectCommand = cmd;
                      try
                      {
                          conn.Open();
                          da.Fill(result);
                      }
                      catch (Exception ex)
                      {
                          throw (ex);
                      }
                      finally
                      {
                          if (conn.State == ConnectionState.Open) conn.Close();
                      }
                  }
              }

              return result;
          }
          public DataTable GetLaporanAll(string periode, string view)
          {
              DataTable result = new DataTable();
              using (SqlConnection conn = new SqlConnection(connectionString))
              {
                  using (SqlCommand cmd = conn.CreateCommand())
                  {
                      cmd.CommandText = "select * from " + view;
                      SqlDataAdapter da = new SqlDataAdapter();
                      da.SelectCommand = cmd;
                      try
                      {
                          conn.Open();
                          da.Fill(result);
                      }
                      catch (Exception ex)
                      {
                          throw (ex);
                      }
                      finally
                      {
                          if (conn.State == ConnectionState.Open) conn.Close();
                      }
                  }
              }

              return result;
          }
     
    }
}
